/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.phoenix.end2end;

import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY;
import static org.apache.phoenix.util.TestUtil.BTABLE_NAME;
import static org.apache.phoenix.util.TestUtil.PTSDB2_NAME;
import static org.apache.phoenix.util.TestUtil.PTSDB_NAME;
import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.Format;
import java.util.Properties;

import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.schema.ConstraintViolationException;
import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.PropertiesUtil;
import org.junit.Test;
import org.junit.experimental.categories.Category;


@Category(ParallelStatsDisabledTest.class)
public class VariableLengthPKIT extends ParallelStatsDisabledIT {
    private static final String DS1 = "1970-01-01 00:58:00";
    private static final Date D1 = toDate(DS1);

    private static Date toDate(String dateString) {
        return DateUtil.parseDate(dateString);
    }
    
    protected static void initGroupByRowKeyColumns(String pTSDBtableName) throws Exception {
        ensureTableCreated(getUrl(),pTSDBtableName, PTSDB_NAME, null, null, null);
        // Insert all rows at ts
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + pTSDBtableName+
                        " (" +
                        "    INST, " +
                        "    HOST," +
                        "    \"DATE\")" +
                        "VALUES (?, ?, CURRENT_DATE())");
        stmt.setString(1, "ab");
        stmt.setString(2, "a");
        stmt.execute();
        stmt.setString(1, "ac");
        stmt.setString(2, "b");
        stmt.execute();
        stmt.setString(1, "ad");
        stmt.setString(2, "a");
        stmt.execute();
        conn.commit();
        conn.close();
    }

    private static void initVarcharKeyTableValues(byte[][] splits, String varcharKeyTestTableName) throws Exception {
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);

        String ddl = "create table " +varcharKeyTestTableName+
                "   (pk varchar not null primary key)";
        createTestTable(getUrl(), ddl, splits, null);
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + varcharKeyTestTableName+
                        "(pk) " +
                        "VALUES (?)");
        stmt.setString(1, "   def");
        stmt.execute();
        stmt.setString(1, "jkl   ");
        stmt.execute();
        stmt.setString(1, "   ghi   ");
        stmt.execute();

        conn.commit();
        conn.close();
    }

    private static void initPTSDBTableValues(byte[][] splits, String pTSDBtableName) throws Exception {
        ensureTableCreated(getUrl(),pTSDBtableName, PTSDB_NAME, splits, null, null);
        // Insert all rows at ts
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + pTSDBtableName +
                        " (" +
                        "    INST, " +
                        "    HOST," +
                        "    \"DATE\"," +
                        "    VAL)" +
                        "VALUES (?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "abc-def-ghi");
        stmt.setDate(3, new Date(System.currentTimeMillis()));
        stmt.setBigDecimal(4, new BigDecimal(.5));
        stmt.execute();
        conn.close();
    }

    private static void initBTableValues(byte[][] splits, String bTableName) throws Exception {
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        ensureTableCreated(getUrl(),bTableName, BTABLE_NAME, splits, null, null);

        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + bTableName+
                        " (" +
                        "    A_STRING, " +
                        "    A_ID," +
                        "    B_STRING," +
                        "    A_INTEGER," +
                        "    B_INTEGER," +
                        "    C_INTEGER," +
                        "    D_STRING," +
                        "    E_STRING)" +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "111");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setInt(5, 10);
        stmt.setInt(6, 1000);
        stmt.setString(7, null);
        stmt.setString(8, "0123456789");
        stmt.execute();

        stmt.setString(1, "abcd");
        stmt.setString(2, "222");
        stmt.setString(3, "xy");
        stmt.setInt(4, 2);
        stmt.setNull(5, Types.INTEGER);
        stmt.setNull(6, Types.INTEGER);
        stmt.execute();

        stmt.setString(3, "xyz");
        stmt.setInt(4, 3);
        stmt.setInt(5, 10);
        stmt.setInt(6, 1000);
        stmt.setString(7, "efg");
        stmt.execute();

        stmt.setString(3, "xyzz");
        stmt.setInt(4, 4);
        stmt.setInt(5, 40);
        stmt.setNull(6, Types.INTEGER);
        stmt.setString(7, null);
        stmt.execute();

        conn.commit();
        conn.close();
    }

    @Test
    public void testSingleColumnScanKey() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING=?";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abc");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertEquals("1", rs.getString(2));
            assertEquals("x", rs.getString(3));
            assertEquals(1, rs.getInt(4));
            assertEquals(10, rs.getInt(5));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSingleColumnGroupBy() throws Exception {
        String pTSDBTableName = generateUniqueName();
        String query = "SELECT INST FROM "+pTSDBTableName+" GROUP BY INST";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);

        try {
            initPTSDBTableValues(null, pTSDBTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testNonfirstColumnGroupBy() throws Exception {
        String pTSDBTableName = generateUniqueName();
        String query = "SELECT HOST FROM "+pTSDBTableName+" WHERE INST='abc' GROUP BY HOST";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initPTSDBTableValues(null, pTSDBTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc-def-ghi", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testGroupByRowKeyColumns() throws Exception {
        String pTSDBTableName = generateUniqueName();
        String query = "SELECT SUBSTR(INST,1,1),HOST FROM "+pTSDBTableName+" GROUP BY SUBSTR(INST,1,1),HOST";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initGroupByRowKeyColumns(pTSDBTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals("a", rs.getString(2));
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals("b", rs.getString(2));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSkipScan() throws Exception {
        String pTSDBTableName = generateUniqueName();
        String query = "SELECT HOST FROM "+pTSDBTableName+" WHERE INST='abc' AND \"DATE\">=TO_DATE('1970-01-01 00:00:00') AND \"DATE\" <TO_DATE('2171-01-01 00:00:00')";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initPTSDBTableValues(null, pTSDBTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc-def-ghi", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSkipMax() throws Exception {
        String pTSDBTableName = generateUniqueName();
        String query = "SELECT MAX(INST),MAX(\"DATE\") FROM "+pTSDBTableName+" WHERE INST='abc' AND \"DATE\">=TO_DATE('1970-01-01 00:00:00') AND \"DATE\" <TO_DATE('2171-01-01 00:00:00')";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initPTSDBTableValues(null, pTSDBTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSkipMaxWithLimit() throws Exception {
        String pTSDBTableName = generateUniqueName();
        String query = "SELECT MAX(INST),MAX(\"DATE\") FROM "+pTSDBTableName+" WHERE INST='abc' AND \"DATE\">=TO_DATE('1970-01-01 00:00:00') AND \"DATE\" <TO_DATE('2171-01-01 00:00:00') LIMIT 2";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initPTSDBTableValues(null, pTSDBTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSingleColumnKeyFilter() throws Exception {
        String bTableName = generateUniqueName();
        // Requires not null column to be projected, since the only one projected in the query is
        // nullable and will cause the no key value to be returned if it is the only one projected.
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE B_STRING=?";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null, bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "xy");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xy", rs.getString(3));
            assertEquals(2, rs.getInt(4));
            assertEquals(0, rs.getInt(5));
            assertTrue(rs.wasNull());
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testMultiColumnEqScanKey() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING=? AND A_ID=? AND B_STRING=?";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null, bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abcd");
            statement.setString(2, "222");
            statement.setString(3, "xy");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xy", rs.getString(3));
            assertEquals(2, rs.getInt(4));
            assertEquals(0, rs.getInt(5));
            assertTrue(rs.wasNull());
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testMultiColumnGTScanKey() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING=? AND A_ID=? AND B_STRING>?";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null, bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abcd");
            statement.setString(2, "222");
            statement.setString(3, "xy");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xyz", rs.getString(3));
            assertEquals(3, rs.getInt(4));
            assertEquals(10, rs.getInt(5));
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xyzz", rs.getString(3));
            assertEquals(4, rs.getInt(4));
            assertEquals(40, rs.getInt(5));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testMultiColumnGTKeyFilter() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING>? AND A_INTEGER>=?";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null, bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abc");
            statement.setInt(2, 4);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xyzz", rs.getString(3));
            assertEquals(4, rs.getInt(4));
            assertEquals(40, rs.getInt(5));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testNullValueEqualityScan() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        // Insert all rows at ts
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES ('', '', ?, 0.5)");
        stmt.setDate(1, D1);
        stmt.execute();
        conn.close();

        // Comparisons against null are always false.
        String query = "SELECT HOST,\"DATE\" FROM "+pTSDBTableName+" WHERE HOST='' AND INST=''";
        url = getUrl();
        conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testVarLengthPKColScan() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES (?, 'y', ?, 0.5)");
        stmt.setString(1, "x");
        stmt.setDate(2, D1);
        stmt.execute();
        stmt.setString(1, "xy");
        stmt.execute();
        conn.close();

        String query = "SELECT HOST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
        url = getUrl();
        conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(D1, rs.getDate(2));
        } finally {
            conn.close();
        }
    }

    @Test
    public void testEscapedQuoteScan() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(), pTSDBTableName, PTSDB_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES (?, 'y', ?, 0.5)");
        stmt.setString(1, "x'y");
        stmt.setDate(2, D1);
        stmt.execute();
        stmt.setString(1, "x");
        stmt.execute();
        conn.close();

        String query1 = "SELECT INST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x''y'";
        String query2 = "SELECT INST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x\\\'y'";
        url = getUrl();
        conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query1);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("x'y", rs.getString(1));
            assertEquals(D1, rs.getDate(2));
            assertFalse(rs.next());

            statement = conn.prepareStatement(query2);
            rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("x'y", rs.getString(1));
            assertEquals(D1, rs.getDate(2));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    private static void initPTSDBTableValues1(String pTSDBTableName) throws Exception {
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES ('x', 'y', ?, 0.5)");
        stmt.setDate(1, D1);
        stmt.execute();
        conn.close();
    }

    @Test
    public void testToStringOnDate() throws Exception {
        String pTSDBTableName = generateUniqueName();
        initPTSDBTableValues1(pTSDBTableName);
        String query = "SELECT HOST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(DateUtil.DEFAULT_DATE_FORMATTER.format(D1), rs.getString(2));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    private static void initPTSDBTableValues2(String pTSDB2TableName, Date d) throws Exception {
        ensureTableCreated(getUrl(),pTSDB2TableName, PTSDB2_NAME, null, null, null);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDB2TableName+"(inst,\"DATE\",val2) VALUES (?, ?, ?)");
        stmt.setString(1, "a");
        stmt.setDate(2, d);
        stmt.setDouble(3, 101.3);
        stmt.execute();
        stmt.setString(1, "a");
        stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 99.7);
        stmt.execute();
        stmt.setString(1, "a");
        stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 105.3);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setDate(2, d);
        stmt.setDouble(3, 88.5);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 89.7);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 94.9);
        stmt.execute();
        conn.close();
    }

    @Test
    public void testRoundOnDate() throws Exception {
        String pTSDB2TableName = generateUniqueName();

        Date date = new Date(System.currentTimeMillis());
        initPTSDBTableValues2(pTSDB2TableName, date);

        String query = "SELECT MAX(val2)"
                + " FROM "+pTSDB2TableName
                + " WHERE inst='a'"
                + " GROUP BY ROUND(\"DATE\",'day',1)"
                + " ORDER BY MAX(val2)"; // disambiguate row order
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(99.7, rs.getDouble(1), 1e-6);
            assertTrue(rs.next());
            assertEquals(101.3, rs.getDouble(1), 1e-6);
            assertTrue(rs.next());
            assertEquals(105.3, rs.getDouble(1), 1e-6);
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testOrderBy() throws Exception {
        String pTSDB2TableName = generateUniqueName();
        Date date = new Date(System.currentTimeMillis());
        initPTSDBTableValues2(pTSDB2TableName, date);

        String query = "SELECT inst,MAX(val2),MIN(val2)"
                + " FROM "+pTSDB2TableName
                + " GROUP BY inst,ROUND(\"DATE\",'day',1)"
                + " ORDER BY inst,ROUND(\"DATE\",'day',1)"
                ;
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals(105.3, rs.getDouble(2), 1e-6);
            assertEquals(105.3, rs.getDouble(3), 1e-6);
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals(101.3, rs.getDouble(2), 1e-6);
            assertEquals(101.3, rs.getDouble(3), 1e-6);
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals(99.7, rs.getDouble(2), 1e-6);
            assertEquals(99.7, rs.getDouble(3), 1e-6);
            assertTrue(rs.next());
            assertEquals("b", rs.getString(1));
            assertEquals(94.9, rs.getDouble(2), 1e-6);
            assertEquals(94.9, rs.getDouble(3), 1e-6);
            assertTrue(rs.next());
            assertEquals("b", rs.getString(1));
            assertEquals(88.5, rs.getDouble(2), 1e-6);
            assertEquals(88.5, rs.getDouble(3), 1e-6);
            assertTrue(rs.next());
            assertEquals("b", rs.getString(1));
            assertEquals(89.7, rs.getDouble(2), 1e-6);
            assertEquals(89.7, rs.getDouble(3), 1e-6);
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSelectCount() throws Exception {
        String pTSDB2TableName = generateUniqueName();
        Date date = new Date(System.currentTimeMillis());
        initPTSDBTableValues2(pTSDB2TableName, date);
        String query = "SELECT COUNT(*)"
                + " FROM "+pTSDB2TableName
                + " WHERE inst='a'";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(3, rs.getInt(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testBatchUpsert() throws Exception {
        String pTSDB2TableName = generateUniqueName();
        Date d = new Date(System.currentTimeMillis());
        ensureTableCreated(getUrl(),pTSDB2TableName, PTSDB2_NAME, null, null, null);
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        String query = "SELECT SUM(val1),SUM(val2),SUM(val3) FROM "+pTSDB2TableName;
        String sql1 = "UPSERT INTO "+pTSDB2TableName+"(inst,\"DATE\",val1) VALUES (?, ?, ?)";
        String sql2 = "UPSERT INTO "+pTSDB2TableName+"(inst,\"DATE\",val2) VALUES (?, ?, ?)";
        String sql3 = "UPSERT INTO "+pTSDB2TableName+"(inst,\"DATE\",val3) VALUES (?, ?, ?)";
        Connection conn = DriverManager.getConnection(getUrl(), props);
        conn.setAutoCommit(false);
        // conn.setAutoCommit(true);

        {
            // verify precondition: SUM(val{1,2,3}) are null
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertNull(rs.getBigDecimal(1));
            assertNull(rs.getBigDecimal(2));
            assertNull(rs.getBigDecimal(3));
            assertFalse(rs.next());
            statement.close();
        }

        {
            PreparedStatement s = conn.prepareStatement(sql1);
            s.setString(1, "a");
            s.setDate(2, d);
            s.setInt(3, 1);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql2);
            s.setString(1, "b");
            s.setDate(2, d);
            s.setInt(3, 1);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql3);
            s.setString(1, "c");
            s.setDate(2, d);
            s.setInt(3, 1);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql1);
            s.setString(1, "a");
            s.setDate(2, d);
            s.setInt(3, 5);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql1);
            s.setString(1, "b");
            s.setDate(2, d);
            s.setInt(3, 5);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql1);
            s.setString(1, "c");
            s.setDate(2, d);
            s.setInt(3, 5);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        conn.commit();
        conn.close();

        // Query at a time after the upsert to confirm they took place
        conn = DriverManager.getConnection(getUrl(), props);
        {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(15, rs.getDouble(1), 1e-6);
            assertEquals(1, rs.getDouble(2), 1e-6);
            assertEquals(1, rs.getDouble(3), 1e-6);
            assertFalse(rs.next());
            statement.close();
        }
    }

    @Test
    public void testSelectStar() throws Exception {
        String pTSDBTableName = generateUniqueName();
        initPTSDBTableValues1(pTSDBTableName);
        String query = "SELECT * FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("x",rs.getString("inst"));
            assertEquals("y",rs.getString("host"));
            assertEquals(D1, rs.getDate("DATE"));
            assertEquals(BigDecimal.valueOf(0.5), rs.getBigDecimal("val"));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testToCharOnDate() throws Exception {
        String pTSDBTableName = generateUniqueName();

        initPTSDBTableValues1(pTSDBTableName);

        String query = "SELECT HOST,TO_CHAR(\"DATE\") FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(DateUtil.DEFAULT_DATE_FORMATTER.format(D1), rs.getString(2));
        } finally {
            conn.close();
        }
    }

    @Test
    public void testToCharWithFormatOnDate() throws Exception {
        String pTSDBTableName = generateUniqueName();

        initPTSDBTableValues1(pTSDBTableName);
        String format = "HH:mm:ss";
        Format dateFormatter = DateUtil.getDateFormatter(format);
        String query = "SELECT HOST,TO_CHAR(\"DATE\",'" + format + "') FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(dateFormatter.format(D1), rs.getString(2));
        } finally {
            conn.close();
        }
    }

    @Test
    public void testToDateWithFormatOnDate() throws Exception {
        String pTSDBTableName = generateUniqueName();

        initPTSDBTableValues1(pTSDBTableName);

        String format = "yyyy-MM-dd HH:mm:ss.S";
        Format dateFormatter = DateUtil.getDateFormatter(format);
        String query = "SELECT HOST,TO_CHAR(\"DATE\",'" + format + "') FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y' and \"DATE\"=TO_DATE(?,'" + format + "')";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, dateFormatter.format(D1));
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(dateFormatter.format(D1), rs.getString(2));
        } finally {
            conn.close();
        }
    }

    @Test
    public void testMissingPKColumn() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        Statement stmt = conn.createStatement();
        try {
            stmt.execute("upsert into "+pTSDBTableName+"(INST,HOST,VAL) VALUES ('abc', 'abc-def-ghi', 0.5)");
            fail();
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.CONSTRAINT_VIOLATION.getErrorCode(), e.getErrorCode());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testNoKVColumn() throws Exception {
        String pTSDBTableName = generateUniqueName();

        ensureTableCreated(getUrl(),pTSDBTableName, BTABLE_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into "+pTSDBTableName+" VALUES (?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        // Succeeds since we have an empty KV
        stmt.execute();
    }

    @Test
    public void testTooShortKVColumn() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, BTABLE_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + pTSDBTableName +
                        " (" +
                        "    A_STRING, " +
                        "    A_ID," +
                        "    B_STRING," +
                        "    A_INTEGER," +
                        "    C_STRING," +
                        "    E_STRING)" +
                        "VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setString(6, "01234");

        try {
            stmt.execute();
        } catch (ConstraintViolationException e) {
            fail("Constraint voilation Exception should not be thrown, the characters have to be padded");
        } finally {
            conn.close();
        }
    }

    @Test
    public void testTooShortPKColumn() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, BTABLE_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + pTSDBTableName+
                        " (" +
                        "    A_STRING, " +
                        "    A_ID," +
                        "    B_STRING," +
                        "    A_INTEGER," +
                        "    C_STRING," +
                        "    E_STRING)" +
                        "VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "12");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setString(6, "0123456789");

        try {
            stmt.execute();
        } catch (ConstraintViolationException e) {
            fail("Constraint voilation Exception should not be thrown, the characters have to be padded");
        } finally {
            conn.close();
        }
    }

    @Test
    public void testTooLongPKColumn() throws Exception {
        String bTableName = generateUniqueName();
        ensureTableCreated(getUrl(),bTableName, BTABLE_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + bTableName+
                        "(" +
                        "    A_STRING, " +
                        "    A_ID," +
                        "    B_STRING," +
                        "    A_INTEGER," +
                        "    C_STRING," +
                        "    E_STRING)" +
                        "VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "abc");
        stmt.setString(6, "0123456789");

        try {
            stmt.execute();
            fail();
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testTooLongKVColumn() throws Exception {
        String bTableName = generateUniqueName();
        ensureTableCreated(getUrl(),bTableName, BTABLE_NAME, null, null, null);
        String url = getUrl(); // Insert at timestamp 0
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " + bTableName+
                        "(" +
                        "    A_STRING, " +
                        "    A_ID," +
                        "    B_STRING," +
                        "    A_INTEGER," +
                        "    C_STRING," +
                        "    D_STRING," +
                        "    E_STRING)" +
                        "VALUES (?, ?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setString(6,"abcd");
        stmt.setString(7, "0123456789");

        try {
            stmt.execute();
            fail();
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testMultiFixedLengthNull() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT B_INTEGER,C_INTEGER,COUNT(1) FROM "+bTableName+" GROUP BY B_INTEGER,C_INTEGER";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null, bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(0, rs.getInt(1));
            assertTrue(rs.wasNull());
            assertEquals(0, rs.getInt(2));
            assertTrue(rs.wasNull());
            assertEquals(1, rs.getLong(3));

            assertTrue(rs.next());
            assertEquals(10, rs.getInt(1));
            assertEquals(1000, rs.getInt(2));
            assertEquals(2, rs.getLong(3));

            assertTrue(rs.next());
            assertEquals(40, rs.getInt(1));
            assertEquals(0, rs.getInt(2));
            assertTrue(rs.wasNull());
            assertEquals(1, rs.getLong(3));

            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSingleFixedLengthNull() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT C_INTEGER,COUNT(1) FROM "+bTableName+" GROUP BY C_INTEGER";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(0, rs.getInt(1));
            assertTrue(rs.wasNull());
            assertEquals(2, rs.getLong(2));

            assertTrue(rs.next());
            assertEquals(1000, rs.getInt(1));
            assertEquals(2, rs.getLong(2));

            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testMultiMixedTypeGroupBy() throws Exception {
        String bTableName = generateUniqueName();
        String query = "SELECT A_ID, E_STRING, D_STRING, C_INTEGER, COUNT(1) FROM "+bTableName+" GROUP BY A_ID, E_STRING, D_STRING, C_INTEGER";
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("111", rs.getString(1));
            assertEquals("0123456789", rs.getString(2));
            assertEquals(null, rs.getString(3));
            assertEquals(1000, rs.getInt(4));
            assertEquals(1, rs.getInt(5));

            assertTrue(rs.next());
            assertEquals("222", rs.getString(1));
            assertEquals("0123456789", rs.getString(2));
            assertEquals(null, rs.getString(3));
            assertEquals(0, rs.getInt(4));
            assertTrue(rs.wasNull());
            assertEquals(2, rs.getInt(5));

            assertTrue(rs.next());
            assertEquals("222", rs.getString(1));
            assertEquals("0123456789", rs.getString(2));
            assertEquals("efg", rs.getString(3));
            assertEquals(1000, rs.getInt(4));
            assertEquals(1, rs.getInt(5));

            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSubstrFunction() throws Exception {
        String bTableName = generateUniqueName();
        String varcharKeyTestTable = generateUniqueName();
        String query[] = {
                "SELECT substr('ABC',-1,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ABC',-4,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ABC',2,4) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ABC',1,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ABC',0,1) FROM "+bTableName+" LIMIT 1",
                // Test for multibyte characters support.
                "SELECT substr('ĎďĒ',0,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ĎďĒ',0,2) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ĎďĒ',1,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ĎďĒ',1,2) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ĎďĒ',2,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ĎďĒ',2,2) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('ĎďĒ',-1,1) FROM "+bTableName+" LIMIT 1",
                "SELECT substr('Ďďɚʍ',2,4) FROM "+bTableName+" LIMIT 1",
                "SELECT pk FROM "+varcharKeyTestTable+" WHERE substr(pk, 0, 3)='jkl'",
        };
        String result[] = {
                "C",
                null,
                "BC",
                "A",
                "A",
                "Ď",
                "Ďď",
                "Ď",
                "Ďď",
                "ď",
                "ďĒ",
                "Ē",
                "ďɚʍ",
                "jkl   ",
        };
        assertEquals(query.length,result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            initVarcharKeyTableValues(null,varcharKeyTestTable);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testRegexReplaceFunction() throws Exception {
        String bTableName = generateUniqueName();
        // NOTE: we need to double escape the "\\" here because conn.prepareStatement would
        // also try to evaluate the escaping. As a result, to represent what normally would be
        // a "\d" in this test, it would become "\\\\d".
        String query[] = {
                "SELECT regexp_replace('', '') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('', 'abc', 'def') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('123abcABC', '[a-z]+') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('123-abc-ABC', '-[a-zA-Z-]+') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('abcABC123', '\\\\d+', '') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('abcABC123', '\\\\D+', '') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('abc', 'abc', 'def') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('abc123ABC', '\\\\d+', 'def') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('abc123ABC', '[0-9]+', '#') FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN regexp_replace('abcABC123', '[a-zA-Z]+') = '123' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT A_STRING FROM "+bTableName+" WHERE A_ID = regexp_replace('abcABC111', '[a-zA-Z]+') LIMIT 1", // 111
                // Test for multibyte characters support.
                "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '[a-zA-Z]+') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '[Ď-ě]+', '#') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '.+', 'replacement') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', 'Ďď', 'DD') FROM "+bTableName+" LIMIT 1",
        };
        String result[] = {
                null,
                null,
                "123ABC",
                "123",
                "abcABC",
                "123",
                "def",
                "abcdefABC",
                "abc#ABC",
                "1",
                "abc", // the first column
                "Ďď Ēĕ ĜĞ ϗϘϛϢ",
                "# # ĜĞ ϗϘϛϢ",
                "replacement",
                "DD Ēĕ ĜĞ ϗϘϛϢ",
        };
        assertEquals(query.length,result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testRegexpSubstrFunction() throws Exception {
        String bTableName = generateUniqueName();
        String query[] = {
                "SELECT regexp_substr('', '', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('', '', 1) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('', 'abc', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('abc', '', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123', '123', 3) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123', '123', -4) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123ABC', '[a-z]+', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123ABC', '[0-9]+', 4) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123ABCabc', '\\\\d+', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123ABCabc', '\\\\D+', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123ABCabc', '\\\\D+', 4) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('123ABCabc', '\\\\D+', 7) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('na11-app5-26-sjl', '[^-]+', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('na11-app5-26-sjl', '[^-]+') FROM "+bTableName+" LIMIT 1",
                // Test for multibyte characters support.
                "SELECT regexp_substr('ĎďĒĕĜĞ', '.+') FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('ĎďĒĕĜĞ', '.+', 3) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('ĎďĒĕĜĞ', '[a-zA-Z]+', 0) FROM "+bTableName+" LIMIT 1",
                "SELECT regexp_substr('ĎďĒĕĜĞ', '[Ď-ě]+', 3) FROM "+bTableName+" LIMIT 1",
        };
        String result[] = {
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                "123",
                "ABCabc",
                "ABCabc",
                "abc",
                "na11",
                "na11",
                "ĎďĒĕĜĞ",
                "ĒĕĜĞ",
                null,
                "Ēĕ",
        };
        assertEquals(query.length,result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testRegexpSubstrFunction2() throws Exception {
        String tTableName = generateUniqueName();
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        String ddl = "create table " + tTableName + " (k INTEGER NOT NULL PRIMARY KEY, name VARCHAR)";
        conn.createStatement().execute(ddl);
        conn.close();

        String dml = "upsert into " + tTableName + " values(?,?)";
        conn = DriverManager.getConnection(url, props);
        PreparedStatement stmt = conn.prepareStatement(dml);
        String[] values = new String[] {"satax","jruls","hrjcu","yqtrv","jjcvw"};
        for (int i = 0; i < values.length; i++) {
            stmt.setInt(1, i+1);
            stmt.setString(2, values[i]);
            stmt.execute();
        }
        conn.commit();
        conn.close();

        // This matches what Oracle returns for regexp_substr, even through
        // it seems oke for "satax", it should return null.
        String query = "select regexp_substr(name,'[^s]+',1) from " + tTableName + " limit 5";
        conn = DriverManager.getConnection(url, props);
        ResultSet rs = conn.createStatement().executeQuery(query);
        int count = 0;
        String[] results = new String[] {"atax","jrul","hrjcu","yqtrv","jjcvw"};
        while (rs.next()) {
            assertEquals(results[count],rs.getString(1));
            count++;
        }
    }

    @Test
    public void testLikeConstant() throws Exception {
        String bTableName = generateUniqueName();
        String query[] = {
                "SELECT CASE WHEN 'ABC' LIKE '' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 'ABC' LIKE 'A_' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 'ABC' LIKE 'A__' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 'AB_C' LIKE 'AB\\_C' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 'ABC%DE' LIKE 'ABC\\%D%' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
        };
        String result[] = {
                "2",
                "2",
                "1",
                "1",
                "1",
        };
        assertEquals(query.length,result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testInListConstant() throws Exception {
        String bTableName = generateUniqueName();

        String query[] = {
                "SELECT CASE WHEN 'a' IN (null,'a') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN NOT 'a' IN (null,'b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 'a' IN (null,'b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN NOT 'a' IN ('c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 1 IN ('foo',2,1) THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN NOT null IN ('c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN NOT null IN (null,'c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN null IN (null,'c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
                "SELECT CASE WHEN 'a' IN (null,1) THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
        };
        String result[] = {
                "1",
                "1",
                "2",
                "1",
                "1",
                "2",
                "2",
                "2",
                "2"
        };
        assertEquals(query.length,result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testLikeOnColumn() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        // Insert all rows at ts
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES (?, ?, ?, 0.5)");
        stmt.setDate(3, D1);

        stmt.setString(1, "a");
        stmt.setString(2, "a");
        stmt.execute();

        stmt.setString(1, "x");
        stmt.setString(2, "a");
        stmt.execute();

        stmt.setString(1, "xy");
        stmt.setString(2, "b");
        stmt.execute();

        stmt.setString(1, "xyz");
        stmt.setString(2, "c");
        stmt.execute();

        stmt.setString(1, "xyza");
        stmt.setString(2, "d");
        stmt.execute();

        stmt.setString(1, "xyzab");
        stmt.setString(2, "e");
        stmt.execute();

        stmt.setString(1, "z");
        stmt.setString(2, "e");
        stmt.execute();

        conn.commit();
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url, props);
        PreparedStatement statement;
        ResultSet rs;
        try {
            // Test 1
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST LIKE 'x%'");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("x", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xy", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

            // Test 2
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST LIKE 'xy_a%'");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

            // Test 3
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST NOT LIKE 'xy_a%'");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("x", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xy", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("z", rs.getString(1));

            assertFalse(rs.next());

            // Test 4
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'xzabc' LIKE 'xy_a%'");
            rs = statement.executeQuery();
            assertFalse(rs.next());

            // Test 5
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'abcdef' LIKE '%bCd%'");
            rs = statement.executeQuery();
            assertFalse(rs.next());

        } finally {
            conn.close();
        }
    }

    @Test
    public void testILikeOnColumn() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        // Insert all rows at ts
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+"(INST, HOST, \"DATE\", VAL, PATTERN VARCHAR) VALUES (?, ?, ?, 0.5, 'x_Z%')");
        stmt.setDate(3, D1);

        stmt.setString(1, "a");
        stmt.setString(2, "a");
        stmt.execute();

        stmt.setString(1, "x");
        stmt.setString(2, "a");
        stmt.execute();

        stmt.setString(1, "xy");
        stmt.setString(2, "b");
        stmt.execute();

        stmt.setString(1, "xyz");
        stmt.setString(2, "c");
        stmt.execute();

        stmt.setString(1, "xyza");
        stmt.setString(2, "d");
        stmt.execute();

        stmt.setString(1, "xyzab");
        stmt.setString(2, "e");
        stmt.execute();

        stmt.setString(1, "z");
        stmt.setString(2, "e");
        stmt.execute();

        conn.commit();
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url, props);
        PreparedStatement statement;
        ResultSet rs;
        try {
            // Test 1
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST ILIKE 'x%'");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("x", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xy", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

            // Test 2
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST ILIKE 'xy_a%'");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

            // Test 3
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST NOT ILIKE 'xy_a%'");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("x", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xy", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("z", rs.getString(1));

            assertFalse(rs.next());

            // Test 4
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'xzabc' ILIKE 'xy_a%'");
            rs = statement.executeQuery();
            assertFalse(rs.next());

            // Test 5
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'abcdef' ILIKE '%bCd%'");
            rs = statement.executeQuery();
            assertTrue(rs.next());

            // Test 5
            statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+"(PATTERN VARCHAR) WHERE INST ILIKE PATTERN");
            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

        } finally {
            conn.close();
        }
    }

    @Test
    public void testIsNullInPK() throws Exception {
        String pTSDBTableName = generateUniqueName();
        ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES ('', '', ?, 0.5)");
        stmt.setDate(1, D1);
        stmt.execute();
        conn.close();

        String query = "SELECT HOST,INST,\"DATE\" FROM "+pTSDBTableName+" WHERE HOST IS NULL AND INST IS NULL AND \"DATE\"=?";
        url = getUrl();
        conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setDate(1, D1);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertNull(rs.getString(1));
            assertNull(rs.getString(2));
            assertEquals(D1, rs.getDate(3));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testLengthFunction() throws Exception {
        String bTableName = generateUniqueName();
        String query[] = {
                "SELECT length('') FROM "+bTableName+" LIMIT 1",
                "SELECT length(' ') FROM "+bTableName+" LIMIT 1",
                "SELECT length('1') FROM "+bTableName+" LIMIT 1",
                "SELECT length('1234') FROM "+bTableName+" LIMIT 1",
                "SELECT length('ɚɦɰɸ') FROM "+bTableName+" LIMIT 1",
                "SELECT length('ǢǛǟƈ') FROM "+bTableName+" LIMIT 1",
                "SELECT length('This is a test!') FROM "+bTableName+" LIMIT 1",
                "SELECT A_STRING FROM "+bTableName+" WHERE length(A_STRING)=3",
        };
        String result[] = {
                null,
                "1",
                "1",
                "4",
                "4",
                "4",
                "15",
                "abc",
        };
        assertEquals(query.length,result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testUpperFunction() throws Exception {
        String bTableName = generateUniqueName();
        String query[] = {
                "SELECT upper('abc') FROM "+bTableName+" LIMIT 1",
                "SELECT upper('Abc') FROM "+bTableName+" LIMIT 1",
                "SELECT upper('ABC') FROM "+bTableName+" LIMIT 1",
                "SELECT upper('ĎďĒ') FROM "+bTableName+" LIMIT 1",
                "SELECT upper('ß') FROM "+bTableName+" LIMIT 1",
        };
        String result[] = {
                "ABC",
                "ABC",
                "ABC",
                "ĎĎĒ",
                "SS",
        };
        assertEquals(query.length, result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testLowerFunction() throws Exception {
        String bTableName = generateUniqueName();
        String query[] = {
                "SELECT lower('abc') FROM "+bTableName+" LIMIT 1",
                "SELECT lower('Abc') FROM "+bTableName+" LIMIT 1",
                "SELECT lower('ABC') FROM "+bTableName+" LIMIT 1",
                "SELECT lower('ĎďĒ') FROM "+bTableName+" LIMIT 1",
                "SELECT lower('ß') FROM "+bTableName+" LIMIT 1",
                "SELECT lower('SS') FROM "+bTableName+" LIMIT 1",
        };
        String result[] = {
                "abc",
                "abc",
                "abc",
                "ďďē",
                "ß",
                "ss",
        };
        assertEquals(query.length, result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testRTrimFunction() throws Exception {
        String bTableName = generateUniqueName();
        String varcharKeyTestTable = generateUniqueName();
        String query[] = {
                "SELECT rtrim('') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim(' ') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim('   ') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim('abc') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim('abc   ') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim('abc   def') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim('abc   def   ') FROM "+bTableName+" LIMIT 1",
                "SELECT rtrim('ĎďĒ   ') FROM "+bTableName+" LIMIT 1",
                "SELECT pk FROM "+varcharKeyTestTable+" WHERE rtrim(pk)='jkl' LIMIT 1",
        };
        String result[] = {
                null,
                null,
                null,
                "abc",
                "abc",
                "abc   def",
                "abc   def",
                "ĎďĒ",
                "jkl   ",
        };
        assertEquals(query.length, result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            initVarcharKeyTableValues(null,varcharKeyTestTable);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testLTrimFunction() throws Exception {
        String bTableName = generateUniqueName();
        String varcharKeyTestTable = generateUniqueName();
        String query[] = {
                "SELECT ltrim('') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim(' ') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim('   ') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim('abc') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim('   abc') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim('abc   def') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim('   abc   def') FROM "+bTableName+" LIMIT 1",
                "SELECT ltrim('   ĎďĒ') FROM "+bTableName+" LIMIT 1",
                "SELECT pk FROM "+varcharKeyTestTable+" WHERE ltrim(pk)='def' LIMIT 1",
        };
        String result[] = {
                null,
                null,
                null,
                "abc",
                "abc",
                "abc   def",
                "abc   def",
                "ĎďĒ",
                "   def",
        };
        assertEquals(query.length, result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            initVarcharKeyTableValues(null,varcharKeyTestTable);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testSubstrFunctionOnRowKeyInWhere() throws Exception {
        String substrTestTableName = generateUniqueName();
        String url = getUrl();
        Connection conn = DriverManager.getConnection(url);
        conn.createStatement().execute("CREATE TABLE "+substrTestTableName+" (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url);
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abc','a')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd','b')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abce','c')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcde','d')");
        conn.commit();
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url);
        ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from "+substrTestTableName+" where substr(s1,1,4) = 'abcd'");
        assertTrue(rs.next());
        assertEquals("abcd",rs.getString(1));
        assertTrue(rs.next());
        assertEquals("abcde",rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testRTrimFunctionOnRowKeyInWhere() throws Exception {
        String substrTestTableName = generateUniqueName();
        String url = getUrl();
        Connection conn = DriverManager.getConnection(url);
        conn.createStatement().execute("CREATE TABLE "+substrTestTableName+" (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url);
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abc','a')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd','b')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd ','c')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd  ','c')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd  a','c')"); // Need TRAVERSE_AND_LEAVE for cases like this
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcde','d')");
        conn.commit();
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url);
        ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from "+substrTestTableName+" where rtrim(s1) = 'abcd'");
        assertTrue(rs.next());
        assertEquals("abcd",rs.getString(1));
        assertTrue(rs.next());
        assertEquals("abcd ",rs.getString(1));
        assertTrue(rs.next());
        assertEquals("abcd  ",rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testLikeFunctionOnRowKeyInWhere() throws Exception {
        String substrTestTableName = generateUniqueName();
        String url = getUrl();
        Connection conn = DriverManager.getConnection(url);
        conn.createStatement().execute("CREATE TABLE "+substrTestTableName+" (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url);
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abc','a')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd','b')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd-','c')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd-1','c')");
        conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abce','d')");
        conn.commit();
        conn.close();

        url = getUrl();
        conn = DriverManager.getConnection(url);
        ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from "+substrTestTableName+" where s1 like 'abcd%1'");
        assertTrue(rs.next());
        assertEquals("abcd-1",rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testTrimFunction() throws Exception {
        String bTableName = generateUniqueName();
        String varcharKeyTestTable = generateUniqueName();
        String query[] = {
                "SELECT trim('') FROM "+bTableName+" LIMIT 1",
                "SELECT trim(' ') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('   ') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('abc') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('   abc') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('abc   ') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('abc   def') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('   abc   def') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('abc   def   ') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('   abc   def   ') FROM "+bTableName+" LIMIT 1",
                "SELECT trim('   ĎďĒ   ') FROM "+bTableName+" LIMIT 1",
                "SELECT pk FROM "+varcharKeyTestTable+" WHERE trim(pk)='ghi'",
        };
        String result[] = {
                null,
                null,
                null,
                "abc",
                "abc",
                "abc",
                "abc   def",
                "abc   def",
                "abc   def",
                "abc   def",
                "ĎďĒ",
                "   ghi   ",
        };
        assertEquals(query.length, result.length);
        String url = getUrl();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(url, props);
        try {
            initBTableValues(null,bTableName);
            initVarcharKeyTableValues(null,varcharKeyTestTable);
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
            conn.close();
        }
    }
}
